﻿CREATE PROCEDURE [dbo].[authors_get_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @firstName nvarchar(256) = null,
    @lastName nvarchar(256) = null
AS
	SELECT
		Authors.AuthorId,
		Authors.FirstName,
		Authors.LastName,     
		CountBooks = (SELECT Count(1) FROM BooksByAuthor WHERE BooksByAuthor.AuthorId=Authors.AuthorId)
	FROM Authors 
	WHERE
		(@firstName IS NULL OR Authors.FirstName LIKE '%' + @firstName + '%') AND
		(@lastName IS NULL OR Authors.LastName LIKE '%' + @lastName + '%')
	ORDER BY
		CASE WHEN @column = 'FirstName' AND @direction = 1 THEN Authors.FirstName END DESC,
		CASE WHEN @column = 'FirstName' AND @direction = 0 THEN Authors.FirstName END ASC,
		CASE WHEN @column = 'LastName' AND @direction = 1 THEN Authors.LastName END DESC,
		CASE WHEN @column = 'LastName' AND @direction = 0 THEN Authors.LastName END ASC,       
		CASE WHEN @column = 'CountBooks' AND @direction = 1 THEN (SELECT Count(1) FROM BooksByAuthor WHERE BooksByAuthor.AuthorId=Authors.AuthorId) END DESC,
		CASE WHEN @column = 'CountBooks' AND @direction = 0 THEN (SELECT Count(1) FROM BooksByAuthor WHERE BooksByAuthor.AuthorId=Authors.AuthorId) END ASC

	OFFSET ((@page-1)*@size) ROWS
	FETCH NEXT @size ROWS ONLY;

Declare @TotalCount int
SELECT @TotalCount= Count(DISTINCT Authors.AuthorId)
FROM Authors
where 
	(@firstName IS NULL OR Authors.FirstName LIKE '%' + @firstName + '%') AND
	(@lastName IS NULL OR Authors.LastName LIKE '%' + @lastName + '%')

RETURN @TotalCount;
